CALENDAR TABLE SCHEMA FIELD DICTIONARY

This page describes the intent and usage of each field in tblEvents, and serves both as a user guide for admins (and publicists), as well as a field dictionary for software development and maintenance.

Each record (row) in the table captures the information for one concert.

The table is instrumented with fields to handle: zipcode locations/distances, public submission of event data with subsequent admin approval (or deletion), and recurring concert dates. The strategies for each of these concepts is detailed below:

ZIPCODE: Zipcodes are necessary for various purposes: in the substitutes db, the musician can specify maximum travel distance and thus a church searching from another location may be too far, and that musician would not be displayed. In the calendar table, the venue zipcode is recorded for purposes of displaying the existance of a concert at a given map location.
In all cases, the Zipcode entered is immediately converted to Geo longitude/latitude for database distance calculations on the fly. This is all internally done with software, and no humans have to worry about Geo locations. For the map locations, the Geo long/lat are converted into pixel offsets from the (top left) origin of the particular map being displayed (which also will have a range/scope (width/height) to filter the range of long/lat can be displayed on the map's extent).

PUBLIC SUBMISSIONS: The intended strategy, if enabled, is to allow anyone to "submit" data for a new concert. (Note that there may be no provision for editing/maintenance features.) The concert information is entered by anyone, and the submitter's email (as given by the submitter, possibly a spammer/hacker) and IP address are recorded along with the submission date. The (list of) admins are notified that there is a new calendar submission. (Note that if there are multiple submissions across calendar events, placements, substitutes, organ-registry, etc., then the admin(s) are notified only once per 24 hours- if there are additional submissions during the next day, they would get a new reminder, thus limiting annoying reminder emails.) The admins assess if it's spam or real (to the best of their knowledge), and approve it or delete it. Note: we may have a spammer list to block IPs that have uploaded junk.
There are two ways to make the policy decision during the design:
1. display immediately, trusting that the bulk of submissions are real. Add a REPORT SPAM button that would take if offline and notify the reviewer(s). If a hacker/joker clicks on REPORT SPAM and the admin has approved it, the REPORT SPAM is ignored.
2. display only after 24 hours or when/if an admin reivews and approves. If admins are on vacation, then after 24 hours, the event would be displayed.
I favor #1, since we trust it, and the public can also police the data.

RECURRING: tblCalendar can capture concerts that are ongoing/recurring ("repeating") in a limited way: They will be displayed separately, in order, for the ones that are active during the displayed month's scope. This repeating feature is not intended to be a sophisticated mechanism, but rather handle the simplest forms like: Fridays; First Sundays; 1st & 3rd Wednesdays; 2nd & 4th Tuesdays; Last Sundays. A separate entry (perhaps with specific concert details for that given date) is needed to be displayed in the month-day calendar. Recurring concerts would show up in the geography map as being "soon" (purportedly "this week"), a simplification to minimize software complexity and debugging (plus it's just not a high-usage and critical feature overall)

tblCalendar Field Definitions/Usages

 

Additional Fields used for Subscriptions

(Most are copied from the above tblEvents to match them up)